package com.zaijiansunwukong.gen.util;

import com.zaijiansunwukong.gen.model.Column;
import com.zaijiansunwukong.gen.model.Table;
import org.springframework.util.StringUtils;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public final class DbUtils {

	private DbUtils() {}

	/**
	 * 主动传参获取数据库链接
	 * 
	 * @param driver
	 * @param url
	 * @param username
	 * @param password
	 * @return
	 */
	public static Connection getConnection(String driver, String url, String username, String password) {
		try {
			Class.forName(driver);
			Properties props = new Properties();
			props.setProperty("user", username);
			props.setProperty("password", password);
			// 设置可以获取remarks信息
			props.setProperty("remarks", "true");
			// 设置可以获取tables
			props.setProperty("useInformationSchema", "true");
			Connection conn = DriverManager.getConnection(url, props);
			return conn;
		} catch (Exception e) {
			System.out.println("找不到数据库驱动");
		}

		return null;
	}

	/**
	 * 获取数据库中的所有表
	 * 
	 * @return
	 */
	public static List<Table> getTables(Connection conn) {
		return getTables(conn, null);
	}

	/**
	 * 获取数据库中的指定表
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public static Table getTable(Connection conn, String tableName) {
		List<Table> tables = getTables(conn, tableName);

		return tables.get(0);
	}

	/**
	 * 获取数据库中的一个或多个表
	 * 
	 * @return
	 */
	public static List<Table> getTables(Connection conn, String tableName) {
		try {
			List<Table> tables = new ArrayList<Table>();
			DatabaseMetaData dbmd = conn.getMetaData();
//			ResultSet rs = dbmd.getTables(conn.getCatalog(), "ZJDXUSER", namingConvert(tableName), new String[] { "TABLE" });
//			ResultSet rs = dbmd.getTables("null", "ZJDXUSER", null, new String[] { "TABLE" });
			ResultSet rs = dbmd.getTables("null", "ZJDXUSER", convent2Underline(tableName).toUpperCase(), new String[] { "TABLE" });
			while (rs.next()) {
				String dbTableName = rs.getString("TABLE_NAME");

				Table table = new Table();
				table.setTableName(dbTableName);
				table.setComment(customModelComment(rs.getString("REMARKS")));
				table.setColumns(getColumns(conn, dbTableName));
				tables.add(table);

//				break;//测试的话 只需要执行一次
			}
			return tables;
		} catch (SQLException e) {
			System.out.println("数据库异常");
		}
		return null;
	}

	/**
	 * 获取一张表对应的字段
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<Column> getColumns(Connection conn, String tableName) {
		try {
			List<Column> columns = new ArrayList<Column>();
			DatabaseMetaData dbmd = conn.getMetaData();
			ResultSet rs = dbmd.getColumns(conn.getCatalog(), "ZJDXUSER", tableName, null);
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				Integer size = rs.getInt("COLUMN_SIZE");
				String type = getColumnType(rs.getString("TYPE_NAME").toLowerCase());
				String comment = "";
				if (rs.getString("REMARKS") != null) {
					comment = rs.getString("REMARKS").trim();
				}
				String isAutoIncrement = rs.getString("IS_AUTOINCREMENT");
				String isNullable = rs.getString("IS_NULLABLE");

				Column column = new Column();
				column.setName(name);
				column.setSize(size);
				column.setType(type);
				column.setComment(comment);
				column.setIsAutoIncrement(isAutoIncrement);
				column.setIsNullable(isNullable);
				column.setPropertyName(convent2Camel(name));
				columns.add(column);
			}
			return columns;
		} catch (SQLException e) {
			System.out.println("数据库异常");
		}
		return null;
	}

	/**
	 * 根据model的Name和sql语句查询数据库,返回set后的model结果
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static List setModelsWithSql(Connection conn, String sql, Class<?> clazz) {
		try {
			sql = "SELECT * FROM " + clazz.getSimpleName().toLowerCase() + " WHERE " + sql;
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List<Column> columns = getColumns(conn, StringUtils.uncapitalize(clazz.getSimpleName()));
			List models = new ArrayList<Column>();
			while (rs.next()) {
				// 构造对象
				Object model = clazz.newInstance();
				for (Column column : columns) {
					setModel(model, column.getName(), rs.getObject(convent2Camel(column.getName())));
				}
				models.add(model);
			}
			rs.close();
			stmt.close();
			return models;
		} catch (Exception e) {
			System.out.println("数据库异常");
		}
		return null;
	}

	/**
	 * 关闭资源
	 * 
	 * @param conn
	 */
	public static void close(Connection conn) {
		if (conn == null) {
			return;
		}
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("关闭资源时发生异常");
		}
	}

	/**
	 * 将值自动set到model中
	 * 
	 * @param model
	 * @param modelField
	 * @param fieldValue
	 */
	private static void setModel(Object model, String modelField, Object fieldValue) {
		modelField = "set" + StringUtils.capitalize(modelField);
		Method[] methods = model.getClass().getMethods();
		for (Method method : methods) {
			if (modelField.equals(method.getName())) {
				Class<?>[] clazz = method.getParameterTypes();
				String type = clazz[0].getName();
				try {
					if (type.equals("java.lang.String")) {
						method.invoke(model, (String) fieldValue);
					} else if (type.equals("java.util.Date")) {
						method.invoke(model, (Date) fieldValue);
					} else if (type.equals("java.lang.Integer")) {
						method.invoke(model, (Integer) fieldValue);
					} else if (type.equals("java.lang.Double")) {
						method.invoke(model, new Double((String) fieldValue));
					} else if (type.equals("java.lang.Long")) {
						method.invoke(model, (Long) fieldValue);
					} else if (type.equals("java.math.BigDecimal")) {
						method.invoke(model, (BigDecimal) fieldValue);
					}
				} catch (Exception e) {
					System.out.println("model自动装载异常");
				}

			}
		}
	}

	/**
	 * 将数据库类型转换成对应的JAVA类型
	 * 
	 * @param type
	 * @return
	 */
	private static String getColumnType(String type) {
		type = type.toLowerCase();
		if ("char".equals(type) || "varchar2".equals(type) || "nvarchar".equals(type) || "text".equals(type)) {
			return "String";
		} else if ("int".equals(type) || "int unsigned".equals(type)) {
			return "Integer";
		} else if ("bigint".equals(type)) {
			return "Long";
		} else if ("timestamp".equals(type) || "date".equals(type) || "datetime".equals(type)) {
			return "Date";
		} else if ("decimal".equals(type)) {
			return "Double";
		} else if ("double".equals(type)) {
			return "Double";
		} else if ("number".equals(type)) {
			return "int";
		}
		return null;
	}

//	/**
//	 * 命名转换：
//	 * 将下划线式命名法改为驼峰命名法
//	 * 反向将驼峰命名法改为下划线式命名法
//	 */
//	public static String namingConvert(String str) {
//		if (str == null || str.isEmpty()) {
//			return null;
//		} else if (str.contains("_")) {
//			Pattern p = Pattern.compile("_[a-z]{0,1}");
//			Matcher match = p.matcher(str);
//			while (match.find()) {
//				String matchStr = match.group();
////				str = StringUtils.replace(str, matchStr, matchStr.toUpperCase(), 1);
//				str = str.toLowerCase();
//				str = str.replaceAll(matchStr, matchStr.toUpperCase());
//			}
//			str = StringUtils.replace(str, "_", "");
//		} else if (str.matches("\\S+[A-Z]+\\S+")) {
//			Pattern p = Pattern.compile("[A-Z]+");
//			Matcher match = p.matcher(str);
//			while (match.find()) {
//				String matchStr = match.group();
////				str = StringUtils.replace(str, matchStr, "_" + matchStr.toLowerCase(), 1);
//				str = str.replaceAll(matchStr, "_" + matchStr.toLowerCase());
//			}
//		}
//		return str;
//	}
	/**
	 * 命名转换：
	 * 将下划线式命名法改为驼峰命名法
	 */
	public static String convent2Camel(String str) {
		if (str == null || str.isEmpty()) {
			return null;
		} else {
			str = str.toLowerCase();
			Pattern p = Pattern.compile("_[a-z]{0,1}");
			Matcher match = p.matcher(str);
			while (match.find()) {
				String matchStr = match.group();
//				str = StringUtils.replace(str, matchStr, matchStr.toUpperCase(), 1);
				str = str.replaceAll(matchStr, matchStr.toUpperCase());
			}
			str = StringUtils.replace(str, "_", "");
		}
		return str;
	}
	/**
	 * 命名转换：
	 * 反向将驼峰命名法改为下划线式命名法
	 */
	public static String convent2Underline(String str) {
		if (str == null || str.isEmpty()) {
			return null;
		} else {
			Pattern p = Pattern.compile("[A-Z]+");
			Matcher match = p.matcher(str);
			while (match.find()) {
				String matchStr = match.group();
//				str = StringUtils.replace(str, matchStr, "_" + matchStr.toLowerCase(), 1);
				str = str.replaceAll(matchStr, "_" + matchStr.toLowerCase());
			}
			str = str.toUpperCase();
		}
		return str;
	}

	/**
	 * 自定义modelComment
	 * 
	 * @param modelComment
	 * @return
	 */
	private static String customModelComment(String modelComment) {

		if (modelComment != null && modelComment.endsWith("表")) {
			modelComment = modelComment.substring(0, modelComment.length() - 1);
			return modelComment;
		}
		return modelComment;
	}

	public static void main(String[] args) {
		String s = convent2Camel("aa_bb_cc");
		System.out.println(s);

		String s2 = convent2Underline("aaBbCc");
		System.out.println(s2);
	}
}
